﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;
using System.Web.Security;

namespace U9BJWebApi.Models
{
    /// <summary>
    /// 数据访问抽象基础类
    /// Copyright (C) 2004-2010
    /// All rights reserved
    /// </summary>
    public abstract class DbHelperSQL
    {
        #region 连接属性
        //数据库连接字符串(web.config来配置)
        private static string connectionString
        {
            get
            {
                return ConnectionString;
            }
        }
        #region 数据库读写分离
        /// <summary>
        ///为了数据库的读写分离，此连接为数据库的读取服务 by:谢承飞 2015年1月21日 11:22:18
        /// </summary>
        private static string Read
        {
            get { return OtherReadconnectionString; }
        }
        /// <summary>
        /// 为了数据库的读写分离，此连接为数据库的读取服务 by:谢承飞 2015年1月21日 11:22:18
        /// </summary>
        private static string _OtherReadconnectionString = "";
        /// <summary>
        /// 为了数据库的读写分离，此连接为数据库的读取服务 by:谢承飞 2015年1月21日 11:22:18
        /// </summary>
        public static string OtherReadconnectionString
        {
            get
            {
                string s = ConfigurationManager.AppSettings["ReadConnectionString"];
                string Encrypt = ConnectionEncrypt;
                //连接字符串是加密配置
                if ((!string.IsNullOrEmpty(s)) && (!string.IsNullOrEmpty(Encrypt)) && (Encrypt == "Y"))
                {
                    s = DESEncrypt.Decrypt(s);
                }
                if (string.IsNullOrEmpty(s))
                {
                    s = _OtherReadconnectionString;
                }
                return s;
            }
            set
            {
                _OtherReadconnectionString = value;
            }
        }
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        //public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, string Read)
        //{
        //    using (SqlConnection connection = new SqlConnection(otherReadconnectionString))
        //    {
        //        DataSet dataSet = new DataSet();
        //        try
        //        {
        //            connection.Open();
        //            SqlDataAdapter sqlDA = new SqlDataAdapter();
        //            sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
        //            sqlDA.Fill(dataSet, tableName);
        //            connection.Close();
        //            connection.Dispose();
        //        }
        //        catch (Exception ex)
        //        {
        //            connection.Close();
        //            connection.Dispose();
        //            LogManager.WriteLog("执行储存过程的时候报错:" + ex.Message + "存储过程名称:" + storedProcName);
        //        }
        //        return dataSet;
        //    }
        //}
        #endregion

        /// <summary>
        /// 连接字符串是否加密
        /// </summary>
        public static string ConnectionEncrypt
        {
            get
            {
                string Encrypt = ConfigurationManager.AppSettings["Encrypt"];
                return Encrypt;
            }
        }
        /// <summary>
        /// 加密连接字符串
        /// </summary>
        /// <param name="ConnStr"></param>
        /// <returns></returns>
        public static string ConnectionStringEncrypt(string ConnStr)
        {
            string Encrypt = ConnectionEncrypt;
            if ((!string.IsNullOrEmpty(ConnStr)) && (!string.IsNullOrEmpty(Encrypt)) && (Encrypt == "Y"))
            {
                ConnStr = DESEncrypt.Encrypt(ConnStr);
            }
            return ConnStr;
        }

        private static string _ConnectionString = "";
        public static string ConnectionString
        {
            get
            {

                //string s = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                string s = Decrypt(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString(), "ConnectionString");
                //string Encrypt = ConnectionEncrypt;
                ////连接字符串是加密配置
                //if ((!string.IsNullOrEmpty(s)) && (!string.IsNullOrEmpty(Encrypt)) && (Encrypt == "Y"))
                //{
                //    s = DESEncrypt.Decrypt(s);
                //}
                //if (string.IsNullOrEmpty(s))
                //{
                //    //s = @"Data Source=10.0.12.39;Initial Catalog=SupplyChain;Persist Security Info=True;uid=sa;pwd=123456";
                //    //server=192.168.0.100;database=SupplyChain;uid=sa;pwd=123456
                //    s = _ConnectionString;
                //}
                return s;
            }
            set
            {
                _ConnectionString = value;
            }
        }

        private static string _UFurl;

        public static string UFurl
        {
            get
            {
                string s = ConfigurationManager.AppSettings["UFConnStr"];
                string Encrypt = ConnectionEncrypt;
                //连接字符串是加密配置
                if ((!string.IsNullOrEmpty(s)) && (!string.IsNullOrEmpty(Encrypt)) && (Encrypt == "Y"))
                {
                    s = DESEncrypt.Decrypt(s);
                }
                if (string.IsNullOrEmpty(s))
                {
                    s = _UFurl;
                }
                return s;
            }
            set
            {
                _UFurl = value;
            }
        }

        private static string _BSYBConn;

        public static string BSYBConn
        {
            get
            {
                string s = ConfigurationManager.AppSettings["BSYBConn"];
                string Encrypt = ConnectionEncrypt;
                //连接字符串是加密配置
                if ((!string.IsNullOrEmpty(s)) && (!string.IsNullOrEmpty(Encrypt)) && (Encrypt == "Y"))
                {
                    s = DESEncrypt.Decrypt(s);
                }
                if (string.IsNullOrEmpty(s))
                {
                    s = _BSYBConn;
                }
                return s;
            }
            set
            {
                _BSYBConn = value;
            }
        }

        public static string ISGROQTY()
        {
            return ConfigurationManager.AppSettings["ISGROQTY"];
        }
        #region  连接26数据库
        public static string ConnectionStringWMS
        {
            get
            {
                string s = ConfigurationManager.AppSettings["ConnectionStringwms"];
                string Encrypt = ConnectionEncrypt;
                //连接字符串是加密配置
                if ((!string.IsNullOrEmpty(s)) && (!string.IsNullOrEmpty(Encrypt)) && (Encrypt == "Y"))
                {
                    s = DESEncrypt.Decrypt(s);
                }
                if (string.IsNullOrEmpty(s))
                {
                    //s = @"Data Source=10.0.12.39;Initial Catalog=SupplyChain;Persist Security Info=True;uid=sa;pwd=123456";
                    //server=192.168.0.100;database=SupplyChain;uid=sa;pwd=123456
                    s = _ConnectionString;
                }
                return s;
            }
            set
            {
                _ConnectionString = value;
            }
        }
        #region 执行SQL，返回条数
        public static object GetSingleWMS(string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionStringWMS))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        throw new Exception(e.Message);
                    }
                }
            }
        }
        #endregion
        #endregion


        public static string Proess(string AClass)
        {
            //Regex regString = new Regex(@"\w+=(?<server>\w+);\w+=(?<database>\w+);\w+=(?<uid>\w+);\w+=(?<pwd>\w+);");
            //Match m = regString.Match(ConnectionString);
            string[] MapFields = ConnectionString.Split(';');
            foreach (string FieldMapName in MapFields)
            {
                string[] Fields = FieldMapName.Split('=');
                if (Fields.Length < 2)
                {
                    continue;
                }
                if (AClass.Trim().ToLower() == Fields[0].Trim().ToLower())
                    return Fields[1].Trim();
            }
            return string.Empty;
        }
        /// <summary>
        /// 服务器名称
        /// </summary>
        public static string Server
        {
            get
            {
                return Proess("server");
            }
        }
        /// <summary>
        /// 数据库名称
        /// </summary>
        public static string Database
        {
            get
            {
                string db = Proess("database");
                if (string.IsNullOrEmpty(db))
                    db = Proess("Initial Catalog");
                return db;
                //return Proess("database");
            }
        }
        /// <summary>
        /// 登录用户名称
        /// </summary>
        public static string User
        {
            get
            {
                return Proess("uid");
            }
        }
        /// <summary>
        /// 登录密码
        /// </summary>
        public static string Password
        {
            get
            {
                return Proess("pwd");
            }
        }
        #endregion

        private static Hashtable myCache = Hashtable.Synchronized(new Hashtable());
        public DbHelperSQL()
        {
        }

        #region 公用方法

        public static int GetMaxID(string FieldName, string TableName)
        {
            string strsql = "select max(" + FieldName + ")+1 from " + TableName;
            object obj = DbHelperSQL.GetSingle(strsql);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }
        public static bool Exists(string strSql)
        {
            object obj = DbHelperSQL.GetSingle(strSql);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        public static bool Exists(string strSql, params SqlParameter[] cmdParms)
        {
            object obj = DbHelperSQL.GetSingle(strSql, cmdParms);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        public static bool Exists(string strSql, SqlParameter[] cmdParms, string connectionString)
        {
            object obj = DbHelperSQL.GetSingle(strSql, cmdParms, connectionString);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        #endregion

        #region  执行简单SQL语句

        public static string ExecuteSql(DataTable dt, string tablename)
        {
            using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction))
            {
                try
                {
                    sbc.DestinationTableName = tablename;
                    sbc.BatchSize = dt.Rows.Count;
                    sbc.WriteToServer(dt);
                    return dt.Rows.Count.ToString();
                }
                catch (Exception e)
                {
                    return "-1";
                    throw e;
                }
            }
        }

        /// <summary>
        /// 执行SQL语句，返回DataTable
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <param name="pars">支持参数化</param>
        /// <returns>DataTable</returns>
        public static DataTable GetDataTableExecuteSql(string SQLString, Dictionary<string, object> pars = null)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    if (pars != null)
                    {
                        foreach (var item in pars)
                        {
                            cmd.Parameters.Add(new SqlParameter(item.Key, item.Value));
                        }
                    }
                    //设置执行超时时间为2分钟
                    cmd.CommandTimeout = 120000;
                    DataTable dt = new DataTable();
                    try
                    {
                        SqlDataAdapter a = new SqlDataAdapter(cmd);
                        a.Fill(dt);
                        connection.Close();
                        connection.Dispose();
                        return dt;
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        connection.Close();
                        connection.Dispose();
                        throw new Exception(E.Message);
                    }
                }
            }
        }

        /// <summary>
        /// 执行SQL语句，返回List
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <param name="pars">支持参数化</param>
        /// <returns>DataTable</returns>
        public static List<T> GetListExecuteSql<T>(string SQLString, Dictionary<string, object> pars = null) where T : class, new()
        {
            List<T> list = null;
            var dt = GetDataTableExecuteSql(SQLString, pars);
            if (dt != null && dt.Rows.Count > 0)
            {
                list = Tolist<T>(dt);
            }
            return list ?? new List<T>();
        }

        static public List<T> Tolist<T>(DataTable dt) where T : class, new()
        {
            Type t = typeof(T);
            PropertyInfo[] PropertyInfo = t.GetProperties();
            List<T> list = new List<T>();

            string typeName = string.Empty;
            foreach (DataRow item in dt.Rows)
            {
                T obj = new T();
                foreach (PropertyInfo s in PropertyInfo)
                {
                    typeName = s.Name;
                    if (dt.Columns.Contains(typeName))
                    {
                        if (!s.CanWrite) continue;

                        object value = item[typeName];
                        if (value == DBNull.Value) continue;

                        if (s.PropertyType == typeof(string))
                        {
                            s.SetValue(obj, value.ToString(), null);
                        }
                        else
                        {
                            s.SetValue(obj, value, null);
                        }
                    }
                }
                list.Add(obj);
            }
            return list;
        }

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        //为了防止 连接数超大 2014/6/16  刘松
                        connection.Close();
                        connection.Dispose();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        connection.Close();
                        connection.Dispose();
                        throw new Exception(E.Message);
                    }
                }
            }
        }
        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSqlConn(string SQLString, string Conn)
        {
            using (SqlConnection connection = new SqlConnection(Conn))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        //为了防止 连接数超大 2014/6/16  刘松
                        connection.Close();
                        connection.Dispose();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        connection.Close();
                        connection.Dispose();
                        throw new Exception(E.Message);
                    }
                }
            }
        }

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSqlByStrConn(string SQLString, string strConn)
        {
            using (SqlConnection connection = new SqlConnection(strConn))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        connection.Close();
                        throw new Exception(E.Message);
                    }
                }
            }
        }

        /// <summary>
        /// 执行SQL语句，设置命令的执行等待时间
        /// </summary>
        /// <param name="SQLString"></param>
        /// <param name="Times"></param>
        /// <returns></returns>
        public static int ExecuteSqlByTime(string SQLString, int Times)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        cmd.CommandTimeout = Times;
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        connection.Close();
                        throw new Exception(E.Message);
                    }
                }
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>	
        /// <param name="bHavingCountRollback">当每条SQL语句执行结果条数如果等于True 小于0就不提交， 等于false就提交</param>	
        public static void ExecuteSqlTran(ArrayList SQLStringList, bool bHavingCountRollback = false)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    bool bCommit = true;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            int count = cmd.ExecuteNonQuery();
                            //没有的话也让他回滚
                            if ((count <= 0) && (bHavingCountRollback))
                            {
                                bCommit = false;
                                break;
                            }
                        }
                    }
                    if (bCommit)
                        tx.Commit();
                    else
                        tx.Rollback();
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }


        public static bool ExecuteSqlTran(ArrayList SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;

                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    bool bCommit = true;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            int count = cmd.ExecuteNonQuery();

                            //没有的话也让他回滚
                            if ((count <= 0))
                            {
                                bCommit = false;
                                break;
                            }
                        }
                    }
                    if (bCommit)
                    {
                        tx.Commit();
                        return true;
                    }
                    else
                    {
                        tx.Rollback();
                        return false;
                    }
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                    return false;
                }
            }
        }
        /// <summary>
        /// 执行多条sql
        /// </summary>
        /// <param name="SQLStringList"></param>
        /// <param name="connstr"></param>
        public static void ExecuteSqlTran(List<string> SQLStringList, string connstr)
        {


            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>		
        public static void ExecuteSqlTran(ArrayList SQLStringList, string strConn)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();

                        }
                    }
                    tx.Commit();
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }
        /// <summary>
        /// 执行带一个存储过程参数的的SQL语句。
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString, string content)
        {
            using (SqlConnection connection = new SqlConnection(content))
            {
                SqlCommand cmd = new SqlCommand(SQLString, connection);
                System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
                myParameter.Value = content;
                cmd.Parameters.Add(myParameter);
                try
                {
                    connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }
        /// <summary>
        /// 执行带一个存储过程参数的的SQL语句。
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
        /// <returns>影响的记录数</returns>
        public static object ExecuteSqlGet(string SQLString, string content)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(SQLString, connection);
                System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
                myParameter.Value = content;
                cmd.Parameters.Add(myParameter);
                try
                {
                    connection.Open();
                    object obj = cmd.ExecuteScalar();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }
        /// <summary>
        /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(strSQL, connection);
                System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
                myParameter.Value = fs;
                cmd.Parameters.Add(myParameter);
                try
                {
                    connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw new Exception(e.Message);
                    }
                }
            }
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <param name="isBSYBConn">isBSYBConn</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString, bool isBSYBConn)
        {
            using (SqlConnection connection = new SqlConnection(isBSYBConn ? BSYBConn : connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw new Exception(e.Message);
                    }
                }
            }
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString, string strConn)
        {
            using (SqlConnection connection = new SqlConnection(strConn))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw new Exception(e.Message);
                    }
                }
            }
        }



        /// <summary>
        /// 获得数据库现在的时间
        /// </summary>
        /// <returns></returns>
        public static DateTime GetDBNow()
        {
            object o = GetSingle("select getdate()");

            DateTime dt = DateTime.Parse(o.ToString());
            return DateTime.Now;
        }

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString)
        {
            //ConnectionString = "Data Source=.;Initial Catalog=PartsPrice_PJ;Persist Security Info=True;User ID=jg01;Password=jg01";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.SelectCommand.CommandTimeout = 1000;
                    command.Fill(ds, "ds");
                    command.Dispose();
                    connection.Dispose();
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    connection.Dispose();
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataTable Query(SqlCommand cmd, string sql)
        {

            DataTable dt = new DataTable();
            try
            {
                cmd.CommandText = sql;
                SqlDataReader dr = null;
                using (dr = cmd.ExecuteReader(CommandBehavior.Default))
                {
                    dt.Load(dr);
                }
            }
            catch (Exception)
            {
                throw;
            }
            return dt;


        }
        public static DataSet Query(Hashtable SQLStringList)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    foreach (var item in SQLStringList.Keys)
                    {
                        string SQLString = (string)SQLStringList[item];
                        SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                        string Key = (string)item;
                        command.Fill(ds, Key);
                    }
                    connection.Close();
                    connection.Dispose();
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    connection.Close();
                    connection.Dispose();
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }
        /// <summary>
        /// 执行查询语句，返回DataSet,设置命令的执行等待时间
        /// </summary>
        /// <param name="SQLString"></param>
        /// <param name="Times"></param>
        /// <returns></returns>
        public static DataSet Query(string SQLString, int Times)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.SelectCommand.CommandTimeout = Times;
                    command.Fill(ds, "ds");
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }
        /// <summary>
        /// 传了连接字符串的
        /// </summary>
        /// <param name="SQLString"></param>
        /// <param name="Con"></param>
        /// <returns></returns>
        public static DataSet Query(string SQLString, string Con)
        {
            using (SqlConnection connection = new SqlConnection(Con))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }
        public static DataSet Query(Hashtable SQLStringList, string UFconn)
        {
            using (SqlConnection connection = new SqlConnection(UFconn))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    foreach (var item in SQLStringList.Keys)
                    {
                        string SQLString = (string)SQLStringList[item];
                        SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                        string Key = (string)item;
                        command.Fill(ds, Key);
                    }
                    connection.Close();
                    connection.Dispose();
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    connection.Close();
                    connection.Dispose();
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }
        /// <summary>
        /// 查询的连接字符串
        /// </summary>
        /// <returns></returns>
        public static string Queryconn()
        {
            DataSet ds = Query("SELECT * FROM  dbo.G_uf WHERE pkid=1 AND delTag=0");
            return ds.Tables[0].Rows[0]["UFconn"].ToString();
        }
        /// <summary>
        /// 获得数据库的名称
        /// </summary>
        /// <returns></returns>
        public static string QueryconnName()
        {
            DataSet ds = Query("SELECT * FROM  dbo.G_uf WHERE pkid=1 AND delTag=0");
            return ds.Tables[0].Rows[0]["dbbase"].ToString();
        }
        #endregion

        #region 执行带参数的SQL语句

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        //2014/6/16 刘松
                        connection.Close();
                        connection.Dispose();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        connection.Close();
                        connection.Dispose();
                        throw new Exception(E.Message);
                    }
                }
            }
        }
        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(string SQLString, SqlParameter[] cmdParms, string connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        int rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        return rows;
                    }
                    catch (System.Data.SqlClient.SqlException E)
                    {
                        throw new Exception(E.Message);
                    }
                }
            }
        }

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public static int ExecuteSql(SqlConnection conn, SqlTransaction trans, SqlCommand cmd, string SQLString, params SqlParameter[] cmdParms)
        {
            try
            {
                PrepareCommand(cmd, conn, trans, SQLString, cmdParms);
                int rows = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return rows;
            }
            catch (System.Data.SqlClient.SqlException E)
            {

                throw new Exception(E.Message);
            }
        }
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(SqlConnection conn, SqlTransaction trans, SqlCommand cmd, string SQLString, params SqlParameter[] cmdParms)
        {

            PrepareCommand(cmd, conn, trans, SQLString, cmdParms);
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                DataSet ds = new DataSet();
                try
                {
                    da.Fill(ds, "ds");
                    cmd.Parameters.Clear();
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }

        }




        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
        public static void ExecuteSqlTran(Hashtable SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        //循环
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString();
                            PrepareCommand(cmd, conn, trans, cmdText, (SqlParameter[])myDE.Value);
                            int val = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }

        public static void ExecuteSqlTran(List<string> SQLStringList, List<List<SqlParameter>> cmdPara)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        //循环
                        for (int i = 0; i < SQLStringList.Count; i++)
                        {
                            string cmdText = SQLStringList[i];
                            PrepareCommand(cmd, conn, trans, cmdText, cmdPara[i]);
                            int val = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }

        public static void ExecuteSqlTran(List<string> SQLStringList, List<List<SqlParameter>> cmdPara, List<string> tables)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        //循环
                        for (int i = 0; i < SQLStringList.Count; i++)
                        {
                            string cmdText = SQLStringList[i];
                            PrepareCommand(cmd, conn, trans, cmdText, cmdPara[i], tables[i]);
                            int val = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }


        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            connection.Dispose();
                            return null;
                        }
                        else
                        {
                            connection.Dispose();
                            return obj;
                        }

                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Dispose();
                        throw new Exception(e.Message);
                    }

                }
            }
        }

        //重载
        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString, SqlParameter[] cmdParms, string connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                        object obj = cmd.ExecuteScalar();
                        cmd.Parameters.Clear();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        throw new Exception(e.Message);
                    }
                }
            }
        }

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                        cmd.Dispose();
                        connection.Close();
                        connection.Dispose();
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        connection.Close();
                        connection.Dispose();
                        throw new Exception(ex.Message);
                    }
                    return ds;
                }
            }
        }
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString, SqlParameter[] cmdParms, string connectionString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    return ds;
                }
            }
        }

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.CommandTimeout = 1000;
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {
                StringBuilder sb = new StringBuilder();
                string tableName = string.Empty;
                string createMan = string.Empty;
                string gid = string.Empty;
                sb.Append("<?xml version=\"1.0\"?>");
                foreach (SqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                    sb.AppendLine("<" + parameter.ParameterName.Replace("@", "") + ">" + parameter.Value + "</" + parameter.ParameterName.Replace("@", "") + ">");
                    if (parameter.ParameterName == "@Gid")
                    {
                        gid = parameter.Value.ToString();
                    }

                    if (parameter.ParameterName == "@ModifyMan" || parameter.ParameterName == "@Editman")
                    {
                        createMan = parameter.Value.ToString();
                    }
                }
                try
                {
                    if (cmdText.IndexOf("insert ", StringComparison.OrdinalIgnoreCase) >= 0 && cmdText.IndexOf(" into ", StringComparison.OrdinalIgnoreCase) >= 0)
                    {
                        string str = cmdText.Substring(cmdText.IndexOf("insert ", StringComparison.OrdinalIgnoreCase), cmdText.IndexOf(" into ", StringComparison.OrdinalIgnoreCase) + 5);
                        if (str.Replace(" ", "").IndexOf("insertinto", StringComparison.OrdinalIgnoreCase) >= 0)
                        {
                            tableName = cmdText.Substring(cmdText.IndexOf(" into ", StringComparison.OrdinalIgnoreCase) + 6);
                            tableName = GetTableName(tableName);
                        }
                        else if (cmdText.IndexOf("update ", StringComparison.OrdinalIgnoreCase) >= 0)
                        {
                            tableName = cmdText.Substring(cmdText.IndexOf("update ", StringComparison.OrdinalIgnoreCase) + 7);
                            tableName = GetTableName(tableName);
                        }
                        else if (cmdText.IndexOf("delete ", StringComparison.OrdinalIgnoreCase) >= 0 && cmdText.IndexOf(" from ", StringComparison.OrdinalIgnoreCase) >= 0)
                        {
                            str = cmdText.Substring(cmdText.IndexOf("delete ", StringComparison.OrdinalIgnoreCase), cmdText.IndexOf(" from ", StringComparison.OrdinalIgnoreCase) + 5);
                            if (str.Replace(" ", "").IndexOf("deletefrom", StringComparison.OrdinalIgnoreCase) >= 0)
                            {
                                tableName = cmdText.Substring(cmdText.IndexOf(" from ", StringComparison.OrdinalIgnoreCase) + 6);
                                tableName = GetTableName(tableName);
                            }
                        }
                    }
                    else if (cmdText.IndexOf("update ", StringComparison.OrdinalIgnoreCase) >= 0)
                    {
                        tableName = cmdText.Substring(cmdText.IndexOf("update ", StringComparison.OrdinalIgnoreCase) + 7);
                        tableName = GetTableName(tableName);
                    }
                    else if (cmdText.IndexOf("delete ", StringComparison.OrdinalIgnoreCase) >= 0 && cmdText.IndexOf(" from ", StringComparison.OrdinalIgnoreCase) >= 0)
                    {
                        string str = cmdText.Substring(cmdText.IndexOf("delete ", StringComparison.OrdinalIgnoreCase), cmdText.IndexOf(" from ", StringComparison.OrdinalIgnoreCase) + 5);
                        if (str.Replace(" ", "").IndexOf("deletefrom", StringComparison.OrdinalIgnoreCase) >= 0)
                        {
                            tableName = cmdText.Substring(cmdText.IndexOf(" from ", StringComparison.OrdinalIgnoreCase) + 6);

                            tableName = GetTableName(tableName);
                        }
                    }
                    if (tableName != string.Empty && tableName != "Sys_OprLog" && tableName != "Sys_WorkLog")
                    {
                        //ILogger log = new Logging.Logger(new LogContent() { CreateMan = createMan, TableName = tableName, SqlCmd = cmdText, Para = sb.ToString(), Gid = gid });
                        //log.LogInfoMessage("");
                    }

                }
                catch
                {

                }
            }
        }

        private static string GetTableName(string str)
        {
            string tableName = string.Empty;
            int i = -1;
            i = str.IndexOf(" ") > i ? str.IndexOf(" ") : i;
            i = (str.IndexOf("(") < i && str.IndexOf("(") > -1) ? str.IndexOf("(") : i;
            if (i > -1)
            {
                tableName = str.Substring(0, i);
            }
            return tableName;
        }
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, List<SqlParameter> cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {
                StringBuilder sb = new StringBuilder();
                string tableName = string.Empty;
                string createMan = string.Empty;
                string gid = string.Empty;
                sb.Append("<?xml version=\"1.0\"?>");
                foreach (SqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                    sb.AppendLine("<" + parameter.ParameterName.Replace("@", "") + ">" + parameter.Value + "</" + parameter.ParameterName.Replace("@", "") + ">");
                    if (parameter.ParameterName == "@Gid")
                    {
                        gid = parameter.Value.ToString();
                    }

                    if (parameter.ParameterName == "@ModifyMan" || parameter.ParameterName == "@Editman")
                    {
                        createMan = parameter.Value.ToString();
                    }

                }
                try
                {
                    if (cmdText.IndexOf("insert ", StringComparison.OrdinalIgnoreCase) >= 0 && cmdText.IndexOf(" into ", StringComparison.OrdinalIgnoreCase) >= 0)
                    {
                        string str = cmdText.Substring(cmdText.IndexOf("insert ", StringComparison.OrdinalIgnoreCase), cmdText.IndexOf(" into ", StringComparison.OrdinalIgnoreCase) + 5);
                        if (str.Replace(" ", "").IndexOf("insertinto", StringComparison.OrdinalIgnoreCase) >= 0)
                        {
                            tableName = cmdText.Substring(cmdText.IndexOf(" into ", StringComparison.OrdinalIgnoreCase) + 6);
                            tableName = GetTableName(tableName);
                        }
                        else if (cmdText.IndexOf("update ", StringComparison.OrdinalIgnoreCase) >= 0)
                        {
                            tableName = cmdText.Substring(cmdText.IndexOf("update ", StringComparison.OrdinalIgnoreCase) + 7);
                            tableName = GetTableName(tableName);
                        }
                        else if (cmdText.IndexOf("delete ", StringComparison.OrdinalIgnoreCase) >= 0 && cmdText.IndexOf(" from ", StringComparison.OrdinalIgnoreCase) >= 0)
                        {
                            str = cmdText.Substring(cmdText.IndexOf("delete ", StringComparison.OrdinalIgnoreCase), cmdText.IndexOf(" from ", StringComparison.OrdinalIgnoreCase) + 5);
                            if (str.Replace(" ", "").IndexOf("deletefrom", StringComparison.OrdinalIgnoreCase) >= 0)
                            {
                                tableName = cmdText.Substring(cmdText.IndexOf(" from ", StringComparison.OrdinalIgnoreCase) + 6);
                                tableName = GetTableName(tableName);
                            }
                        }
                    }
                    else if (cmdText.IndexOf("update ", StringComparison.OrdinalIgnoreCase) >= 0)
                    {
                        tableName = cmdText.Substring(cmdText.IndexOf("update ", StringComparison.OrdinalIgnoreCase) + 7);
                        tableName = GetTableName(tableName);
                    }
                    else if (cmdText.IndexOf("delete ", StringComparison.OrdinalIgnoreCase) >= 0 && cmdText.IndexOf(" from ", StringComparison.OrdinalIgnoreCase) >= 0)
                    {
                        string str = cmdText.Substring(cmdText.IndexOf("delete ", StringComparison.OrdinalIgnoreCase), cmdText.IndexOf(" from ", StringComparison.OrdinalIgnoreCase) + 5);
                        if (str.Replace(" ", "").IndexOf("deletefrom", StringComparison.OrdinalIgnoreCase) >= 0)
                        {
                            tableName = cmdText.Substring(cmdText.IndexOf(" from ", StringComparison.OrdinalIgnoreCase) + 6);

                            tableName = GetTableName(tableName);
                        }
                    }
                    if (tableName != string.Empty && tableName != "Sys_OprLog" && tableName != "Sys_WorkLog")
                    {
                        //ILogger log = new Logging.Logger(new LogContent() { CreateMan = createMan, TableName = tableName, SqlCmd = cmdText, Para = sb.ToString(), Gid = gid });
                        //log.LogInfoMessage("");
                    }

                }
                catch
                {

                }

            }
        }

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, List<SqlParameter> cmdParms, string tableName)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {
                StringBuilder sb = new StringBuilder();
                string createMan = string.Empty;
                string gid = string.Empty;
                sb.Append("<?xml version=\"1.0\"?>");
                foreach (SqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                    sb.AppendLine("<" + parameter.ParameterName.Replace("@", "") + ">" + parameter.Value + "</" + parameter.ParameterName.Replace("@", "") + ">");
                    if (parameter.ParameterName == "@Gid")
                    {
                        gid = parameter.Value.ToString();
                    }

                    if (parameter.ParameterName == "@ModifyMan" || parameter.ParameterName == "@Editman")
                    {
                        createMan = parameter.Value.ToString();
                    }

                }
                try
                {
                    //    SqlParameter[] sp = new SqlParameter[]
                    //{
                    // new SqlParameter("@tableName",""),
                    // new SqlParameter("@sqlCmd",cmdText),
                    // new SqlParameter("@para",sb.ToString()),
                    // new SqlParameter("@gid",gid),
                    // new SqlParameter("@createman",createMan)
                    //};
                    // DbHelperSQL.ExecuteSql("insert into sys_log(tableName,sqlCmd,para,gid,createman) values (@tableName,@sqlCmd,@para,@gid,@createman) ", sp);
                    //ILogger log = new Logging.Logger(new LogContent() { CreateMan = createMan, TableName = tableName, SqlCmd = cmdText, Para = sb.ToString(), Gid = gid });
                    //log.LogInfoMessage("");

                }
                catch
                {

                }

            }
        }

        #endregion

        #region 存储过程操作


        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            //string strsql = "select ValueStr from Sys_Constant where ConstCode = '" + storedProcName + "' and AllowUsed='Y' ";
            //object objvaluestr = DbHelperSQL.GetSingle(strsql);
            //if (objvaluestr != null && objvaluestr != "")
            {
                using (SqlConnection connection = new SqlConnection(Read))
                {
                    DataSet dataSet = new DataSet();
                    try
                    {
                        connection.Open();
                        SqlDataAdapter sqlDA = new SqlDataAdapter();
                        sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                        sqlDA.Fill(dataSet, tableName);
                        //2015/1/28  谢承飞修改
                        connection.Close();
                        connection.Dispose();
                    }
                    catch (Exception ex)
                    {
                        connection.Close();
                        connection.Dispose();
                        //LogManager.WriteLog("执行储存过程的时候报错:" + ex.Message + "存储过程名称:" + storedProcName);
                        //throw new Exception("执行储存过程-" + storedProcName + "-的时候报错，消息：" + ex.Message);


                    }
                    return dataSet;
                }
            }
            //else
            //{
            //    using (SqlConnection connection = new SqlConnection(connectionString))
            //    {
            //        DataSet dataSet = new DataSet();
            //        try
            //        {
            //            connection.Open();
            //            SqlDataAdapter sqlDA = new SqlDataAdapter();
            //            sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
            //            sqlDA.Fill(dataSet, tableName);
            //            //2014/6/16  刘松修改
            //            connection.Close();
            //            connection.Dispose();
            //        }
            //        catch (Exception ex)
            //        {
            //            connection.Close();
            //            connection.Dispose();
            //            //LogManager.WriteLog("执行储存过程的时候报错:" + ex.Message + "存储过程名称:" + storedProcName);
            //            //throw new Exception("执行储存过程-" + storedProcName + "-的时候报错，消息：" + ex.Message);


            //        }
            //        return dataSet;
            //    }
            //}
        }

        /// <summary>
        /// 执行存储过程 罗琴 新增 2016-3-14
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public static DataSet RunProcedure(SqlConnection connection, SqlTransaction trans, SqlCommand command, string storedProcName, IDataParameter[] parameters, string tableName)
        {
            DataSet dataSet = new DataSet();
            try
            {
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, trans, command, storedProcName, parameters);
                sqlDA.Fill(dataSet, tableName);
            }
            catch (Exception ex)
            {
                //LogManager.WriteLog("执行储存过程的时候报错:" + ex.Message + "存储过程名称:" + storedProcName);
            }
            return dataSet;
        }
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public static DataSet RunProcedureByConn(string storedProcName, IDataParameter[] parameters, string tableName, string connstr)
        {
            using (SqlConnection connection = new SqlConnection(connstr))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                //sqlDA.SelectCommand.CommandTimeout = 0;
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <param name="Conn">数据库的连接</param>
        /// <returns></returns>
        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, string Conn)
        {
            using (SqlConnection connection = new SqlConnection(Conn))
            {
                DataSet dataSet = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter sqlDA = new SqlDataAdapter();
                    sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                    sqlDA.Fill(dataSet, tableName);
                    //2015/01/22 谢承飞修改
                    connection.Close();
                    connection.Dispose();
                }
                catch (Exception ex)
                {
                    connection.Close();
                    connection.Dispose();
                    //LogManager.WriteLog("执行储存过程的时候报错:" + ex.Message + "存储过程名称:" + storedProcName);
                }
                return dataSet;
            }
        }

        public static DataSet RunProcedure1(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();

                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.SelectCommand.CommandTimeout = 0;
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }
        public static DataSet RunProcedureBS(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();

                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.SelectCommand.CommandTimeout = 0;
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }
        /// <summary>
        /// 执行存储过程26
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public static DataSet Procedure(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionStringWMS))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();

                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.SelectCommand.CommandTimeout = 0;
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="connect"></param>
        /// <returns></returns>
        public static void Procedure(string storedProcName, string connect)
        {
            SqlCommand cmd = new SqlCommand();
            //定义对象资源保存的范围，一量using范围结束，将释放对方所占的资源
            using (SqlConnection conn = new SqlConnection(connect)) //注意因为conn已经在SqlHelper.cs中使用，这里不能重名所以改用conn1
            {
                //打开链接
                conn.Open();
                //调用执行方法,因为没有参数，所以最后一项设为null
                //int val = SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, storedProcName, null);
            }
        }

        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.SelectCommand.CommandTimeout = Times;
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }

        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, string Conn, int Times)
        {
            using (SqlConnection connection = new SqlConnection(Conn))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.SelectCommand.CommandTimeout = Times;
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }

        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            command.CommandTimeout = 1000;
            if (null != parameters)
            {
                foreach (SqlParameter parameter in parameters)
                {
                    if (parameter != null)
                    {
                        // 检查未分配值的输出参数,将其分配以DBNull.Value.
                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                            (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        command.Parameters.Add(parameter);
                    }
                }
            }
            return command;
        }

        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        private static SqlCommand BuildQueryCommand(SqlConnection connection, SqlTransaction trans, SqlCommand command, string storedProcName, IDataParameter[] parameters)
        {
            command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            command.CommandTimeout = 1000;
            command.Transaction = trans;
            if (null != parameters)
            {
                foreach (SqlParameter parameter in parameters)
                {
                    if (parameter != null)
                    {
                        // 检查未分配值的输出参数,将其分配以DBNull.Value.
                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                            (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        command.Parameters.Add(parameter);
                    }
                }
            }
            return command;
        }

        /// <summary>
        /// 执行存储过程，返回影响的行数		
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="rowsAffected">影响的行数</param>
        /// <returns></returns>
        public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                int result = 0;
                connection.Open();
                SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
                //command.CommandTimeout = 0;
                rowsAffected = command.ExecuteNonQuery();
                if (command.Parameters.IndexOf("ReturnValue") >= 0)
                    result = (int)command.Parameters["ReturnValue"].Value;
                if (command.Parameters.IndexOf("@RETURN_VALUE") >= 0)
                    result = (int)command.Parameters["@RETURN_VALUE"].Value;
                //Connection.Close();
                return result;
            }
        }
        /// <summary>
        /// 得到系统唯一号码；		
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="rowsAffected">影响的行数</param>
        /// <returns></returns>
        public static string NewBillNo(string BillType)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string result;
                connection.Open();//sp_Pub_NewBillNo
                //获取存储过程参数
                SqlParameter[] commandParameters = GetSpParameters("sp_bill_no", true);
                //command.Parameters[0].Value = null;
                commandParameters[1].Value = BillType;
                SqlCommand command = BuildIntCommand(connection, "sp_bill_no", commandParameters);

                int rowsAffected = command.ExecuteNonQuery();
                int iRlt = (int)command.Parameters["@RETURN_VALUE"].Value;
                result = (string)command.Parameters["@sBillNo"].Value;
                if (iRlt != 0)
                {
                    new Exception("获取单据号失败:" + iRlt.ToString());
                }
                //Connection.Close();
                return result;
            }
        }


        /// <summary>
        /// 根据指定的时间产生单号
        /// </summary>
        /// <param name="BillType"></param>
        /// <param name="DateType">yyyy-MM-dd HH:MM:SS</param>
        /// <returns></returns>
        public static string NewBillNo(string BillType, DateTime DateType)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string result;

                connection.Open();//sp_Pub_NewBillNo
                //获取存储过程参数
                SqlParameter[] commandParameters = GetSpParameters("[sp_bill_no_FDJ_pm]", true);
                //command.Parameters[0].Value = null;
                commandParameters[1].Value = BillType;
                commandParameters[2].Value = DateType.ToString("yyyy-MM-dd hh:mm:ss");
                SqlCommand command = BuildIntCommand(connection, "[sp_bill_no_FDJ_pm]", commandParameters);


                int rowsAffected = command.ExecuteNonQuery();
                int iRlt = (int)command.Parameters["@RETURN_VALUE"].Value;
                result = (string)command.Parameters["@sBillNo"].Value;
                if (iRlt != 0)
                {
                    new Exception("获取单据号失败:" + iRlt.ToString());
                }
                //Connection.Close();
                return result;
            }
        }

        public static string SingleNewBillNo(string BillType, DateTime DateType)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string result;

                connection.Open();//sp_Pub_NewBillNo
                //获取存储过程参数
                SqlParameter[] commandParameters = GetSpParameters("[sp_bill_no_pm]", true);
                //command.Parameters[0].Value = null;
                commandParameters[1].Value = BillType;
                commandParameters[2].Value = DateType.ToString("yyyy-MM-dd hh:mm:ss");
                SqlCommand command = BuildIntCommand(connection, "[sp_bill_no_pm]", commandParameters);


                int rowsAffected = command.ExecuteNonQuery();
                int iRlt = (int)command.Parameters["@RETURN_VALUE"].Value;
                result = (string)command.Parameters["@sBillNo"].Value;
                if (iRlt != 0)
                {
                    new Exception("获取单据号失败:" + iRlt.ToString());
                }
                //Connection.Close();
                return result;
            }
        }
        //部品订单号生成
        public static string BPNewBillNo(string BillType, string factory)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string result;

                connection.Open();
                //获取存储过程参数
                SqlParameter[] commandParameters = GetSpParameters("[Sp_Client_bill_no]", true);
                //command.Parameters[0].Value = null;
                commandParameters[1].Value = factory;
                commandParameters[2].Value = BillType;

                SqlCommand command = BuildIntCommand(connection, "[Sp_Client_bill_no]", commandParameters);


                int rowsAffected = command.ExecuteNonQuery();
                int iRlt = (int)command.Parameters["@RETURN_VALUE"].Value;
                result = (string)command.Parameters["@sBillNo"].Value;
                if (iRlt != 0)
                {
                    new Exception("获取单据号失败:" + iRlt.ToString());
                }
                //Connection.Close();
                return result;
            }
        }



        /// <summary>
        /// 得到系统唯一号码；		
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="rowsAffected">影响的行数</param>
        /// <returns></returns>
        public static string NewLogNo()
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string result;

                connection.Open();
                //获取存储过程参数
                SqlParameter[] commandParameters = GetSpParameters("sp_Sys_NewBillNo", true);
                commandParameters[1].Value = "Sys_Log";
                commandParameters[2].Value = "";
                commandParameters[3].Value = "Y";
                commandParameters[4].Value = "";
                SqlCommand command = BuildIntCommand(connection, "sp_Sys_NewBillNo", commandParameters);
                int rowsAffected = command.ExecuteNonQuery();
                int iRlt = (int)command.Parameters["@RETURN_VALUE"].Value;
                result = (string)command.Parameters["@sBillNo"].Value;
                if (iRlt != 0)
                {
                    //exception
                }
                //Connection.Close();
                return result;
            }
        }

        public static string RunProcedure(string Type, string gid, string proc, string ConnString)
        {
            string msg = string.Empty;
            using (SqlConnection conn = new SqlConnection(ConnString))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand(proc, conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Type", "" + Type + "");
                    cmd.Parameters.AddWithValue("@Gid", "" + gid + "");
                    SqlParameter parOutput = cmd.Parameters.Add("@Flag", SqlDbType.NVarChar, 200);　　//定义输出参数	   
                    parOutput.Direction = ParameterDirection.Output;　　//参数类型为Output	   
                    conn.Open();
                    cmd.CommandTimeout = 0;
                    cmd.ExecuteNonQuery();
                    msg = parOutput.Value.ToString();//显示输出参数的值	    
                }
                catch (Exception ex)
                {
                    msg = "X";
                    //LogManager.WriteLog("执行存储过程报错:" + ex.Message);
                }
            }
            return msg;
        }





        /// <summary>
        /// 创建 SqlCommand 对象实例(用来返回一个整数值)	
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand 对象实例</returns>
        private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.Parameters.Add(new SqlParameter("ReturnValue",
                SqlDbType.Int, 4, ParameterDirection.ReturnValue,
                false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return command;
        }
        #endregion

        #region 获取存储过程参数
        /// <summary>
        /// 获取存储过程参数数组
        /// </summary>
        /// <param name="spName">存储过程名</param>
        /// <param name="hasReturn">是否包含返回类型的参数</param>
        /// <returns>参数数组</returns>
        private static SqlParameter[] GetSpParameterSet(string spName, bool hasReturn)
        {
            using (SqlConnection _connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    //为命令指定连接
                    cmd.Connection = _connection;

                    //如果数据连接未开启，打开它
                    if (_connection.State != ConnectionState.Open)
                    {
                        _connection.Open();
                    }
                    //指定CommandText为存储过程名
                    cmd.CommandText = spName;
                    cmd.CommandType = CommandType.StoredProcedure;
                    //从存储过程中检索参数，填充到Parameters集
                    try
                    {
                        SqlCommandBuilder.DeriveParameters(cmd);
                    }
                    catch (Exception e)
                    {
                        //执行失败
                        //从异常中获取失败信息
                        string msg = e.Message;
                        //调用写日志方法
                        //Trace.Write("DeriveParameters", "Error", spName + "|" + msg);
                    }

                    //如果有返回值
                    if (!hasReturn)
                    {
                        cmd.Parameters.RemoveAt(0);
                    }

                    //将Parameters集中的参数复制到参数数组
                    SqlParameter[] para = new SqlParameter[cmd.Parameters.Count];
                    cmd.Parameters.CopyTo(para, 0);

                    return para;
                }
            }
        }

        /// <summary>
        /// 深拷贝缓存中的参数序列
        /// </summary>
        /// <param name="originalParameters">源参数序列</param>
        /// <returns>拷贝后的参数序列</returns>
        private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
        {
            //深拷贝缓存中的参数序列
            SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];

            for (int i = 0, j = originalParameters.Length; i < j; i++)
            {
                clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone();
            }

            return clonedParameters;
        }

        /// <summary>
        /// 获取存储过程参数
        /// </summary>
        /// <param name="spName">存储过程名</param>
        /// <param name="hasReturn">是否包含返回值</param>
        /// <returns>返回参数序列</returns>
        public static SqlParameter[] GetSpParameters(string spName, bool hasReturn)
        {
            //缓存HashTable的主键
            string cacheKey = spName + (hasReturn ? ":hasReturn" : "");
            //从Hachtable中获取参数
            SqlParameter[] cachedParameters = (SqlParameter[])myCache[cacheKey];
            if (cachedParameters == null || cachedParameters.Length < 1)
            {
                //如果Hachtable中没有，调用获取存储过程参数数组方法
                cachedParameters = (SqlParameter[])(myCache[cacheKey] = GetSpParameterSet(spName, hasReturn));
            }

            return CloneParameters(cachedParameters);
        }
        #endregion

        #region 更新表
        /// <summary>
        /// 保存单表数据
        /// </summary>
        /// <param name="ADataTable">内存表</param>
        public static void SaveTable(DataTable ADataTable)
        {
            if (ADataTable == null)
            {
                throw new Exception("DataTable不存在!");
            }
            SaveTable(ADataTable, ADataTable.TableName);
        }
        /// <summary>
        /// 保存带表名的单表
        /// </summary>
        /// <param name="ADataTable"></param>
        /// <param name="ATableName"></param>
        public static void SaveTable(DataTable ADataTable, string ATableName)
        {
            string strSelect = BuilderSelect(ADataTable, ATableName);

            SaveTable(ADataTable, ATableName, strSelect);
        }

        public static void SaveTable2(DataTable ADataTable, string ATableName)
        {
            string strSelect = BuilderSelect(ADataTable, ATableName);

            SaveTable2(ADataTable, ATableName, strSelect);
        }
        public static void SaveTableConstr(DataTable ADataTable, string ATableName)
        {
            string strSelect = BuilderSelectConstr(ADataTable, ATableName);
            SaveTableConstr(ADataTable, ATableName, strSelect);
        }
        /// <summary>
        /// 保存自定制查询SQL的单表
        /// </summary>
        /// <param name="ADataTable"></param>
        /// <param name="ATableName"></param>
        /// <param name="AUpdateSQL"></param>
        public static void SaveTable(DataTable ADataTable, string ATableName, string AUpdateSQL)
        {
            if (ADataTable == null)
            {
                throw new Exception("DataTable不存在!");
            }
            //下面是初始化数据库连接自己设置吧 
            SqlConnection sconnGlobal = new SqlConnection(ConnectionString);
            sconnGlobal.Open();
            SqlTransaction stracGlobal = sconnGlobal.BeginTransaction();      //可以添加事物处理 
            SqlDataAdapter sdaGlobal;

            string strSelect = AUpdateSQL;

            sdaGlobal = new SqlDataAdapter();
            sdaGlobal.SelectCommand = new SqlCommand(strSelect, sconnGlobal);
            sdaGlobal.SelectCommand.Transaction = stracGlobal;

            //重要的地方来了 
            SqlCommandBuilder scombuilder = new SqlCommandBuilder(sdaGlobal);
            scombuilder.ConflictOption = ConflictOption.OverwriteChanges;
            scombuilder.SetAllValues = false;
            scombuilder.RefreshSchema();
            //该生成可供执行的数据库操作命令 
            sdaGlobal.UpdateCommand = scombuilder.GetUpdateCommand();
            sdaGlobal.InsertCommand = scombuilder.GetInsertCommand();
            sdaGlobal.DeleteCommand = scombuilder.GetDeleteCommand();

            //可以设置一把TimeOut,随便设置 
            sdaGlobal.UpdateCommand.CommandTimeout = 1000;
            sdaGlobal.InsertCommand.CommandTimeout = 1000;
            sdaGlobal.DeleteCommand.CommandTimeout = 1000;

            //可以加上你们事物 
            sdaGlobal.UpdateCommand.Transaction = stracGlobal;
            sdaGlobal.DeleteCommand.Transaction = stracGlobal;
            sdaGlobal.InsertCommand.Transaction = stracGlobal;
            try
            {
                //可以进行更新了 
                sdaGlobal.AcceptChangesDuringUpdate = false;
                sdaGlobal.Update(ADataTable);
                ADataTable.AcceptChanges();
                //关闭数据连接什么的,我也不写了,偷懒 
                stracGlobal.Commit();


            }
            catch (Exception ex)
            {
                //异常处理自己写 
                stracGlobal.Rollback();
                throw new Exception(ex.Message);
            }
        }

        public static void SaveTable2(DataTable ADataTable, string ATableName, string AUpdateSQL)
        {
            if (ADataTable == null)
            {
                throw new Exception("DataTable不存在!");
            }
            //下面是初始化数据库连接自己设置吧 
            SqlConnection sconnGlobal = new SqlConnection(ConnectionString);
            sconnGlobal.Open();
            SqlTransaction stracGlobal = sconnGlobal.BeginTransaction();      //可以添加事物处理 
            SqlDataAdapter sdaGlobal;

            string strSelect = AUpdateSQL;

            sdaGlobal = new SqlDataAdapter();
            sdaGlobal.SelectCommand = new SqlCommand(strSelect, sconnGlobal);
            sdaGlobal.SelectCommand.Transaction = stracGlobal;

            //重要的地方来了 
            SqlCommandBuilder scombuilder = new SqlCommandBuilder(sdaGlobal);
            scombuilder.ConflictOption = ConflictOption.OverwriteChanges;
            scombuilder.SetAllValues = false;
            scombuilder.RefreshSchema();
            //该生成可供执行的数据库操作命令 
            sdaGlobal.UpdateCommand = scombuilder.GetUpdateCommand();
            sdaGlobal.InsertCommand = scombuilder.GetInsertCommand();
            sdaGlobal.DeleteCommand = scombuilder.GetDeleteCommand();

            //可以设置一把TimeOut,随便设置 
            sdaGlobal.UpdateCommand.CommandTimeout = 1000;
            sdaGlobal.InsertCommand.CommandTimeout = 1000;
            sdaGlobal.DeleteCommand.CommandTimeout = 1000;

            //可以加上你们事物 
            sdaGlobal.UpdateCommand.Transaction = stracGlobal;
            sdaGlobal.DeleteCommand.Transaction = stracGlobal;
            sdaGlobal.InsertCommand.Transaction = stracGlobal;
            try
            {
                //可以进行更新了 
                sdaGlobal.AcceptChangesDuringUpdate = false;
                sdaGlobal.Update(ADataTable);
                ADataTable.AcceptChanges();
                //关闭数据连接什么的,我也不写了,偷懒 
                stracGlobal.Commit();

                //MessageBox.Show("保存成功");
            }
            catch (Exception ex)
            {
                //异常处理自己写 
                stracGlobal.Rollback();
                throw new Exception(ex.Message);
            }
        }
        public static void SaveTableConstr(DataTable ADataTable, string ATableName, string AUpdateSQL)
        {
            if (ADataTable == null)
            {
                throw new Exception("DataTable不存在!");
            }
            //下面是初始化数据库连接自己设置吧 
            SqlConnection sconnGlobal = new SqlConnection(ConnectionStringWMS);
            sconnGlobal.Open();
            SqlTransaction stracGlobal = sconnGlobal.BeginTransaction();      //可以添加事物处理 
            SqlDataAdapter sdaGlobal;

            string strSelect = AUpdateSQL;

            sdaGlobal = new SqlDataAdapter();
            sdaGlobal.SelectCommand = new SqlCommand(strSelect, sconnGlobal);
            sdaGlobal.SelectCommand.Transaction = stracGlobal;

            //重要的地方来了 
            SqlCommandBuilder scombuilder = new SqlCommandBuilder(sdaGlobal);
            scombuilder.ConflictOption = ConflictOption.OverwriteChanges;
            scombuilder.SetAllValues = false;
            scombuilder.RefreshSchema();
            //该生成可供执行的数据库操作命令 
            sdaGlobal.UpdateCommand = scombuilder.GetUpdateCommand();
            sdaGlobal.InsertCommand = scombuilder.GetInsertCommand();
            sdaGlobal.DeleteCommand = scombuilder.GetDeleteCommand();

            //可以设置一把TimeOut,随便设置 
            sdaGlobal.UpdateCommand.CommandTimeout = 1000;
            sdaGlobal.InsertCommand.CommandTimeout = 1000;
            sdaGlobal.DeleteCommand.CommandTimeout = 1000;

            //可以加上你们事物 
            sdaGlobal.UpdateCommand.Transaction = stracGlobal;
            sdaGlobal.DeleteCommand.Transaction = stracGlobal;
            sdaGlobal.InsertCommand.Transaction = stracGlobal;
            try
            {
                //可以进行更新了 
                sdaGlobal.AcceptChangesDuringUpdate = false;
                sdaGlobal.Update(ADataTable);
                ADataTable.AcceptChanges();
                //关闭数据连接什么的,我也不写了,偷懒 
                stracGlobal.Commit();

                //MessageBox.Show("保存成功");
            }
            catch (Exception ex)
            {
                //异常处理自己写 
                stracGlobal.Rollback();
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// 为单表建立更新语句
        /// </summary>
        /// <param name="ADataTable"></param>
        /// <param name="ATableName"></param>
        public static string BuilderSelect(DataTable ADataTable, string ATableName)
        {
            if (ADataTable == null)
            {
                throw new Exception("DataTable不存在!");
            }
            //做一个DataTable,取数据库中表名和主键用的 
            DataTable dtColStat;
            DataTable dtCols;

            //取得对象的标示种子 
            string ssqlcolsstat = "select distinct objs.name as tblname,cols.name as colname,cols.colstat as colstat"
                + " from sysobjects objs join syscolumns cols on (objs.id=cols.id) "
                + " where colstat='1' and objs.name ='{0}'";

            string ssqlcols = "select distinct objs.name as tblname,cols.name as colname,cols.colstat as colstat"
                + " from sysobjects objs join syscolumns cols on (objs.id=cols.id) "
                + " where objs.name ='{0}'";

            ssqlcolsstat = string.Format(ssqlcolsstat, ATableName);
            ssqlcols = string.Format(ssqlcols, ATableName);
            try
            {
                DataSet ds = Query(ssqlcolsstat);
                dtColStat = ds.Tables[0];
                ds = Query(ssqlcols);
                dtCols = ds.Tables[0];
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
            }
            string strSelect;         //准备用SelectCommand 

            //string strStatColName = string.Empty;   //准备得到的Column名称    
            string strColName = string.Empty;
            StringBuilder sbCols = new StringBuilder(128);
            //下面拼装Select语句,取出主键和要更新的所有Column 
            sbCols.Append("select ");
            foreach (DataColumn dc in ADataTable.Columns)
            {
                strColName = dc.ColumnName;
                DataRow[] drs = dtColStat.Select("colname='" + strColName + "'");
                DataRow[] drcols = dtCols.Select("colname='" + strColName + "'");
                if ((drs.Length <= 0) && (drcols.Length > 0)) //非标示种子列和有效的数据库列；
                {
                    sbCols.Append(strColName);
                    sbCols.Append(",");
                }
            }
            if (sbCols.Length > 7)
                sbCols.Remove(sbCols.Length - 1, 1);
            sbCols.Append(" from ");
            sbCols.Append(ATableName);
            strSelect = sbCols.ToString();

            return strSelect;
        }
        public static string BuilderSelectConstr(DataTable ADataTable, string ATableName)
        {
            string connStr = null;
            //One.Common.DBUtility.DbHelperSQL.ConnectionStringWMS;
            if (ADataTable == null)
            {
                throw new Exception("DataTable不存在!");
            }
            //做一个DataTable,取数据库中表名和主键用的 
            DataTable dtColStat;
            DataTable dtCols;

            //取得对象的标示种子 
            string ssqlcolsstat = "select distinct objs.name as tblname,cols.name as colname,cols.colstat as colstat"
                + " from sysobjects objs join syscolumns cols on (objs.id=cols.id) "
                + " where colstat='1' and objs.name ='{0}'";

            string ssqlcols = "select distinct objs.name as tblname,cols.name as colname,cols.colstat as colstat"
                + " from sysobjects objs join syscolumns cols on (objs.id=cols.id) "
                + " where objs.name ='{0}'";

            ssqlcolsstat = string.Format(ssqlcolsstat, ATableName);
            ssqlcols = string.Format(ssqlcols, ATableName);
            try
            {
                DataSet ds = Query(ssqlcolsstat, connStr);
                dtColStat = ds.Tables[0];
                ds = Query(ssqlcols, connStr);
                dtCols = ds.Tables[0];
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
            }
            string strSelect;         //准备用SelectCommand 

            //string strStatColName = string.Empty;   //准备得到的Column名称    
            string strColName = string.Empty;
            StringBuilder sbCols = new StringBuilder(128);
            //下面拼装Select语句,取出主键和要更新的所有Column 
            sbCols.Append("select ");
            foreach (DataColumn dc in ADataTable.Columns)
            {
                strColName = dc.ColumnName;
                DataRow[] drs = dtColStat.Select("colname='" + strColName + "'");
                DataRow[] drcols = dtCols.Select("colname='" + strColName + "'");
                if ((drs.Length <= 0) && (drcols.Length > 0)) //非标示种子列和有效的数据库列；
                {
                    sbCols.Append(strColName);
                    sbCols.Append(",");
                }
            }
            if (sbCols.Length > 7)
                sbCols.Remove(sbCols.Length - 1, 1);
            sbCols.Append(" from ");
            sbCols.Append(ATableName);
            strSelect = sbCols.ToString();

            return strSelect;
        }
        /// <summary>
        /// 更新多表
        /// </summary>
        /// <param name="ADataTable"></param>
        public static void SaveTables(DataTable[] ADataTable)
        {
            if (ADataTable == null)
            {
                throw new Exception("ADataTable不存在!");
            }
            string[] UpdateSQL;
            StringBuilder strBuilder = new StringBuilder();
            ArrayList sqlList = new ArrayList();
            foreach (DataTable dt in ADataTable)
            {
                string s = BuilderSelect(dt, dt.TableName);
                sqlList.Add(s);
            }
            UpdateSQL = (string[])sqlList.ToArray(typeof(string));
            SaveTables(ADataTable, UpdateSQL);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="ADataTable"></param>
        /// <param name="AUpdateSQL"></param>
        public static void SaveTables(DataTable[] ADataTable, string[] AUpdateSQL)
        {
            if ((ADataTable == null) && (AUpdateSQL == null))
            {
                throw new Exception("DataTable不存在!");
            }
            if (ADataTable.Length != AUpdateSQL.Length)
            {
                throw new Exception("长度不一致!");
            }
            //下面是初始化数据库连接自己设置吧 
            SqlConnection sconnGlobal = new SqlConnection(ConnectionString);
            sconnGlobal.Open();
            SqlTransaction stracGlobal = sconnGlobal.BeginTransaction();      //可以添加事物处理 
            SqlDataAdapter sdaGlobal;

            string strSelect = "";

            sdaGlobal = new SqlDataAdapter();
            sdaGlobal.SelectCommand = new SqlCommand(strSelect, sconnGlobal);
            sdaGlobal.SelectCommand.Transaction = stracGlobal;
            try
            {
                for (int i = 0; i < ADataTable.Length; i++)
                {
                    DataTable dt = ADataTable[i];
                    strSelect = AUpdateSQL[i];
                    sdaGlobal.SelectCommand.CommandText = strSelect;
                    sdaGlobal.AcceptChangesDuringUpdate = true;
                    //重要的地方来了 
                    SqlCommandBuilder scombuilder = new SqlCommandBuilder(sdaGlobal);
                    scombuilder.ConflictOption = ConflictOption.OverwriteChanges;
                    scombuilder.SetAllValues = false;
                    scombuilder.RefreshSchema();
                    //该生成可供执行的数据库操作命令 
                    sdaGlobal.UpdateCommand = scombuilder.GetUpdateCommand();
                    sdaGlobal.InsertCommand = scombuilder.GetInsertCommand();
                    sdaGlobal.DeleteCommand = scombuilder.GetDeleteCommand();

                    //可以设置一把TimeOut,随便设置 
                    sdaGlobal.UpdateCommand.CommandTimeout = 1000;
                    sdaGlobal.InsertCommand.CommandTimeout = 1000;
                    sdaGlobal.DeleteCommand.CommandTimeout = 1000;

                    //可以加上你们事物 
                    sdaGlobal.UpdateCommand.Transaction = stracGlobal;
                    sdaGlobal.DeleteCommand.Transaction = stracGlobal;
                    sdaGlobal.InsertCommand.Transaction = stracGlobal;

                    //可以进行更新了 
                    sdaGlobal.AcceptChangesDuringUpdate = false;
                    sdaGlobal.Update(dt);
                    //dt.AcceptChanges();
                }
                //关闭数据连接什么的,我也不写了,偷懒 
                stracGlobal.Commit();
                for (int i = 0; i < ADataTable.Length; i++)
                {
                    DataTable dt = ADataTable[i];
                    dt.AcceptChanges();
                }
            }
            catch (Exception ex)
            {
                //异常处理自己写 
                stracGlobal.Rollback();
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region 取得系统表信息

        public static DataTable GetTableInfo(string ATableName)
        {
            string sql = string.Empty;

            sql += " SELECT  ";
            sql += " TableName=O.name,  ";
            sql += " TableDesc=ISNULL(CASE WHEN C.column_id=1 THEN PTB.[value] END, N'N'),  ";
            sql += " ColumnCode=C.column_id,  ";
            sql += " ColumnName=C.name,  ";
            sql += " PrimaryKey=ISNULL(IDX.PrimaryKey, N'N'),  ";
            sql += " [Identity]=CASE WHEN C.is_identity=1 THEN N'Y' ELSE N'N' END,  ";
            sql += " Computed=CASE WHEN C.is_computed=1 THEN N'Y' ELSE N'N' END,  ";
            sql += " Type=T.name,  ";
            sql += " Length=C.max_length,  ";
            sql += " Precision=C.precision,  ";
            sql += " Scale=C.scale,  ";
            sql += " NullAble=CASE WHEN C.is_nullable=1 THEN N'Y' ELSE N'N' END,  ";
            sql += " [Default]=ISNULL(D.definition, N''),  ";
            sql += " ColumnDesc=ISNULL(PFD.[value], N''),  ";
            sql += " IndexName=ISNULL(IDX.IndexName, N''),  ";
            sql += " IndexSort=ISNULL(IDX.Sort,N''),  ";
            sql += " CreateDate=O.Create_Date,  ";
            sql += " ModifyDate=O.Modify_date  ";
            sql += " FROM sys.columns C  ";
            sql += " INNER JOIN sys.objects O  ";
            sql += " ON C.[object_id]=O.[object_id]  ";
            sql += " AND O.type='U'  ";
            sql += " AND O.is_ms_shipped=0  ";
            sql += " INNER JOIN sys.types T  ";
            sql += " ON C.user_type_id=T.user_type_id  ";
            sql += " LEFT JOIN sys.default_constraints D  ";
            sql += " ON C.[object_id]=D.parent_object_id  ";
            sql += " AND C.column_id=D.parent_column_id  ";
            sql += " AND C.default_object_id=D.[object_id]  ";
            sql += " LEFT JOIN sys.extended_properties PFD  ";
            sql += " ON PFD.class=1   ";
            sql += " AND C.[object_id]=PFD.major_id   ";
            sql += " AND C.column_id=PFD.minor_id  ";
            //sql += " --             AND PFD.name='Caption'    ";
            sql += " LEFT JOIN sys.extended_properties PTB  ";
            sql += " ON PTB.class=1   ";
            sql += " AND PTB.minor_id=0   ";
            sql += " AND C.[object_id]=PTB.major_id  ";
            //sql += " --             AND PFD.name='Caption'    ";
            sql += " LEFT JOIN     ";
            sql += " (  ";
            sql += " SELECT   ";
            sql += " IDXC.[object_id],  ";
            sql += " IDXC.column_id,  ";
            sql += " Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')  ";
            sql += " WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,  ";
            sql += " PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'Y'ELSE N'N' END,  ";
            sql += " IndexName=IDX.Name  ";
            sql += " FROM sys.indexes IDX  ";
            sql += " INNER JOIN sys.index_columns IDXC  ";
            sql += " ON IDX.[object_id]=IDXC.[object_id]  ";
            sql += " AND IDX.index_id=IDXC.index_id  ";
            sql += " LEFT JOIN sys.key_constraints KC  ";
            sql += " ON IDX.[object_id]=KC.[parent_object_id]  ";
            sql += " AND IDX.index_id=KC.unique_index_id  ";
            sql += " INNER JOIN    ";
            sql += " (  ";
            sql += " SELECT [object_id], Column_id, index_id=MIN(index_id)  ";
            sql += " FROM sys.index_columns  ";
            sql += " GROUP BY [object_id], Column_id  ";
            sql += " ) IDXCUQ  ";
            sql += " ON IDXC.[object_id]=IDXCUQ.[object_id]  ";
            sql += " AND IDXC.Column_id=IDXCUQ.Column_id  ";
            sql += " AND IDXC.index_id=IDXCUQ.index_id  ";
            sql += " ) IDX  ";
            sql += " ON C.[object_id]=IDX.[object_id]  ";
            sql += " AND C.column_id=IDX.column_id   ";
            //sql += " WHERE O.name=N'p_materiel'         ";
            sql += " WHERE O.name = '" + ATableName + "'         ";
            sql += " ORDER BY O.name,C.column_id   ";

            DataSet ds = Query(sql);
            DataTable dt = ds.Tables[0];
            return dt;
        }

        public static DataTable GetTableKeyInfo(string ATableName)
        {
            string sql = string.Empty;

            sql += " select o.name as TableName,c.name as ColumnName, k.colid as ColumnOrder, ";
            sql += " k.keyno as OrderOrder, t.name as ColumnType ";
            sql += " from sysindexes i ";
            sql += " join sysindexkeys k on i.id = k.id and i.indid = k.indid ";
            sql += " join sysobjects o on i.id = o.id ";
            sql += " join syscolumns c on i.id=c.id and k.colid = c.colid ";
            sql += " join systypes t on c.xusertype=t.xusertype ";
            sql += " where o.xtype = 'U' ";
            sql += " and O.name = '" + ATableName + "'         ";
            sql += " and exists(select 1 from sysobjects where xtype = 'PK' and name = i.name) ";
            sql += " order by o.name,k.colid";

            DataSet ds = Query(sql);
            DataTable dt = ds.Tables[0];
            return dt;
        }
        #endregion

        #region 转换 数据库字段类型 为 c#类型

        /// <summary>
        /// 转换数据库字段类型为c#类型
        /// </summary>
        /// <param name="dbtype">数据库字段类型</param>
        /// <returns>c#类型</returns>		
        public static string DbTypeToCS(string dbtype)
        {
            string CSType = "string";
            switch (dbtype.ToLower().Trim())
            {
                case "varchar":
                case "varchar2":
                case "nvarchar":
                case "nvarchar2":
                case "char":
                case "nchar":
                case "text":
                case "ntext":
                case "string":
                    CSType = "string";
                    break;
                case "date":
                case "datetime":
                case "smalldatetime":
                case "DateTime":
                    CSType = "DateTime";
                    break;
                case "smallint":
                case "int":
                case "number":
                case "bigint":
                case "tinyint":
                    CSType = "int";
                    break;
                case "float":
                case "numeric":
                case "decimal":
                case "money":
                case "smallmoney":
                case "real":
                    CSType = "decimal";
                    break;
                case "bit":
                case "bool":
                    CSType = "bool";
                    break;
                case "binary":
                case "varbinary":
                case "image":
                case "raw":
                case "long":
                case "long raw":
                case "blob":
                case "bfile":
                case "byte[]":
                    CSType = "byte[]";
                    break;
                case "uniqueidentifier":
                case "Guid":
                    CSType = "Guid";
                    break;
                default:
                    CSType = "string";
                    break;
            }
            return CSType;
        }
        #endregion

        #region  导入方法
        public static DataTable GetTables(OleDbConnection conn)
        {
            if ((conn != null) && (conn.State == ConnectionState.Open))
            {
                conn.Close();
            }
            conn.Open();
            DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            conn.Close();
            return schemaTable;
        }
        public static DataSet GetExcelDataSet(string path, string table)
        {
            string sql = string.Empty;
            OleDbConnection con = null;
            try
            {
                string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" +
               "Data Source= " + path + ";" +
               "Extended Properties=Excel 12.0;HDR=YES; IMEX=1";
                //LogManager.WriteLog(strConn);
                con = new OleDbConnection(strConn);
                con.Open();

                sql = "select * from [" + table + "$]";
                if (table == string.Empty)
                {

                    DataTable dt = GetTables(con);
                    if (dt.Rows.Count > 0)
                    {

                        DataRow dr = dt.Rows[0];
                        table = (string)dr["TABLE_NAME"];
                        sql = "select * from [" + table + "]";
                    }
                }

            }
            catch (Exception ex)
            {
                if (con != null)
                {

                    if ((con.State == ConnectionState.Open))
                        con.Close();
                    con.Dispose();
                }

                //LogManager.WriteLog(ex);

                //LogManager.WriteLog(strConn);
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source= " + path + ";" +
                "Extended Properties=Excel 8.0;";// HDR=YES; IMEX=1
                //Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + path + ';Extended Properties=Excel 8.0;';
                //LogManager.WriteLog(strConn);
                con = new OleDbConnection(strConn);
                con.Open();
                sql = "select * from [" + table + "$]";
                if (table == string.Empty)
                {
                    DataTable dt = GetTables(con);
                    if (dt.Rows.Count > 0)
                    {
                        DataRow dr = dt.Rows[0];
                        table = (string)dr["TABLE_NAME"];
                        sql = "select * from [" + table + "]";
                    }
                }
            }
            if (table == string.Empty)
            {
                table = "Sheet";
                sql = "select * from [Sheet$]";
            }
            OleDbDataAdapter da = new OleDbDataAdapter(sql, con);
            DataSet ds = new DataSet();
            da.Fill(ds, table);
            if (con != null)
            {
                con.Close();
                con.Dispose();
            }
            return ds;

        }
        #endregion
        public static bool ExecuteSqlTranBool(List<string> SQLStringList)
        {
            bool bo = false;
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                    bo = true;
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    bo = false;
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
            return bo;
        }
        public static void ExecuteSqlTran(List<string> SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }

        #region   需要手工关闭连接的
        /// <summary>
        /// 执行存储过程  (使用该方法切记要手工关闭SqlDataReader和连接)
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlDataReader returnReader;
            connection.Open();
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.CommandType = CommandType.StoredProcedure;
            returnReader = command.ExecuteReader();
            //Connection.Close(); 不能在此关闭，否则，返回的对象将无法使用            
            return returnReader;
        }
        /// <summary>
        /// 执行存储过程  (使用该方法切记要手工关闭SqlDataReader和连接)
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters, bool isBSYBConn)
        {
            SqlConnection connection = new SqlConnection(isBSYBConn ? BSYBConn : connectionString);
            SqlDataReader returnReader;
            connection.Open();
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.CommandType = CommandType.StoredProcedure;
            returnReader = command.ExecuteReader();
            //Connection.Close(); 不能在此关闭，否则，返回的对象将无法使用            
            return returnReader;
        }
        /// <summary>
        /// 执行查询语句，返回SqlDataReader(使用该方法切记要手工关闭SqlDataReader和连接)
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string strSQL)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(strSQL, connection);
            try
            {
                connection.Open();
                SqlDataReader myReader = cmd.ExecuteReader();
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
            //finally //不能在此关闭，否则，返回的对象将无法使用
            //{
            //	cmd.Dispose();
            //	connection.Close();
            //}	
        }
        /// <summary>
        /// 执行查询语句，返回SqlDataReader (使用该方法切记要手工关闭SqlDataReader和连接)
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();
            try
            {

                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                SqlDataReader myReader = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                return myReader;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }
            //finally //不能在此关闭，否则，返回的对象将无法使用
            //{
            //	cmd.Dispose();
            //	connection.Close();
            //}	
        }
        #endregion
        //罗琴 新增 2016-3-15
        public static string RunProcedureGetOutput(SqlConnection connection, SqlTransaction trans, SqlCommand command, string storedProcName, IDataParameter[] parameters)
        {

            command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            command.CommandTimeout = 1000;
            command.Transaction = trans;
            SqlParameter parOutput = new SqlParameter(); ;
            string ss = string.Empty;
            try
            {
                if (null != command.Parameters)
                {
                    foreach (SqlParameter parameter in parameters)
                    {
                        if (parameter != null)
                        {
                            if (parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input)
                            {
                                command.Parameters.Add(parameter);
                            }
                            else if (parameter.Direction == ParameterDirection.Output)
                            { parOutput = command.Parameters.Add(parameter); }
                        }
                    }
                }
                command.ExecuteNonQuery();
                ss = parOutput.Value.ToString();//显示输出参数的值	    
            }
            catch (Exception ex)
            {
                //LogManager.WriteLog("执行存储过程保存" + storedProcName + "|" + ex.Message + ex.StackTrace);
                ss = "X";
            }
            return ss;
        }
        public static void CopySaveTable(DataTable dt, string tablename)
        {
            if (dt == null)
            {
                throw new Exception("Table 不存在");
            }
            try
            {
                using (SqlBulkCopy sbc = new SqlBulkCopy(ConnectionString))
                {
                    sbc.BulkCopyTimeout = 0;
                    sbc.DestinationTableName = tablename;
                    sbc.BulkCopyTimeout = 0;
                    sbc.NotifyAfter = dt.Rows.Count;
                    sbc.WriteToServer(dt);
                    sbc.Close();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        public static string InsertDataToMES(string Type, string gid, string proc)
        {
            string msg = string.Empty;
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand(proc, conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Type", "" + Type + "");
                    cmd.Parameters.AddWithValue("@Gid", "" + gid + "");
                    SqlParameter parOutput = cmd.Parameters.Add("@Flag", SqlDbType.NVarChar, 200);　　//定义输出参数	   
                    parOutput.Direction = ParameterDirection.Output;　　//参数类型为Output	   
                    conn.Open();
                    cmd.CommandTimeout = 0;
                    cmd.ExecuteNonQuery();
                    msg = parOutput.Value.ToString();//显示输出参数的值	    
                }
                catch (Exception ex)
                {
                    msg = "X";
                    //LogManager.WriteLog("执行存储过程" + proc + ",参数：" + gid + " 报错:" + ex.Message);
                }
            }
            return msg;
        }

        public static IList<T> ConvertTo<T>(DataTable table)
        {
            if (table == null)
            {
                return null;
            }

            List<DataRow> rows = new List<DataRow>();

            foreach (DataRow row in table.Rows)
            {
                rows.Add(row);
            }

            return ConvertTo<T>(rows);
        }

        public static IList<T> ConvertTo<T>(IList<DataRow> rows)
        {
            IList<T> list = null;

            if (rows != null)
            {
                list = new List<T>();

                foreach (DataRow row in rows)
                {
                    T item = CreateItem<T>(row);
                    list.Add(item);
                }
            }

            return list;
        }

        public static T CreateItem<T>(DataRow row)
        {
            T obj = default(T);
            if (row != null)
            {
                obj = Activator.CreateInstance<T>();

                foreach (DataColumn column in row.Table.Columns)
                {
                    PropertyInfo prop = obj.GetType().GetProperty(column.ColumnName);
                    try
                    {
                        object value1 = row[column.ColumnName];
                        prop.SetValue(obj, value1, null);
                    }
                    catch (Exception ex)
                    {

                        throw;
                    }

                }
            }

            return obj;
        }

        public static long ExecuteSqlRtLong(string strSql)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand sqlcmd = new SqlCommand(strSql, connection);

            try
            {

                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }
                long rtn = Convert.ToInt64(sqlcmd.ExecuteScalar());
                connection.Close();
                sqlcmd.Dispose();
                return rtn;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                throw new Exception(e.Message);
            }




        }


        /// <summary>
        /// DES数据解密
        /// </summary>
        /// <param name="targetValue"></param>
        /// <param name="key"></param>
        /// <returns></returns>
        public static string Decrypt(string targetValue, string key)
        {
            if (string.IsNullOrEmpty(targetValue))
            {
                return string.Empty;
            }
            // 定义DES加密对象
            var des = new DESCryptoServiceProvider();
            int len = targetValue.Length / 2;
            var inputByteArray = new byte[len];
            int x, i;
            for (x = 0; x < len; x++)
            {
                i = Convert.ToInt32(targetValue.Substring(x * 2, 2), 16);
                inputByteArray[x] = (byte)i;
            }
            // 通过两次哈希密码设置对称算法的初始化向量   
            des.Key = Encoding.ASCII.GetBytes(FormsAuthentication.HashPasswordForStoringInConfigFile
                                                    (FormsAuthentication.HashPasswordForStoringInConfigFile(key, "md5").
                                                        Substring(0, 8), "sha1").Substring(0, 8));
            // 通过两次哈希密码设置算法的机密密钥   
            des.IV = Encoding.ASCII.GetBytes(FormsAuthentication.HashPasswordForStoringInConfigFile
                                                    (FormsAuthentication.HashPasswordForStoringInConfigFile(key, "md5")
                                                        .Substring(0, 8), "md5").Substring(0, 8));
            // 定义内存流
            var ms = new MemoryStream();
            // 定义加密流
            var cs = new CryptoStream(ms, des.CreateDecryptor(), CryptoStreamMode.Write);
            cs.Write(inputByteArray, 0, inputByteArray.Length);
            cs.FlushFinalBlock();
            return Encoding.Default.GetString(ms.ToArray());
        }
    }

}
